package com.example.datatransfer.jdbc;


import com.example.datatransfer.callback.TargetCallback;
import com.example.datatransfer.entity.FieldInfo;
import com.example.datatransfer.entity.TableInfo;
import com.example.datatransfer.enums.DataTypeEnum;
import com.example.datatransfer.enums.NullableEnum;
import com.example.datatransfer.enums.YesOrNoEnum;

import java.math.BigDecimal;
import java.sql.*;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.function.BinaryOperator;


public class JdbcTargetTool {


	 static final String JDBC_DRRIVER = "com.mysql.jdbc.Driver";

	 static final String DB_URL = "jdbc:mysql://localhost:3306/transform";

	 static final String USER_NAME = "root";

	 static final String PASSWORD = "root";

	 private JdbcConfig config;


	 private JdbcTargetTool() {
		 try {
		 	config = new JdbcConfig(DB_URL,USER_NAME,PASSWORD);
			Class.forName(JDBC_DRRIVER);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	 }
	 private static class Holder{
		 private static JdbcTargetTool instance = new JdbcTargetTool();
	 }

	 public static JdbcTargetTool getInstance() {
		 return Holder.instance;
	 }

	 private Connection createConnection() {
		 Connection conn = null;
		 try {
			 conn = DriverManager.getConnection(config.getDbUrl(), config.getUserName(), config.getPassword());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return conn;
	 }
	 public <T> T execute(StatementOption<T> option,String sql){
		 Connection conn = null;
		 Statement statement = null;
		 try {

			 conn = createConnection();
			 statement = conn.createStatement();
			 return option.doStatement(statement,sql);
		 }catch(Exception e) {
			 e.printStackTrace();
		 }finally {
			 try {
			 	if(null != statement){
					statement.close();
				}
				 if(null != conn){
					 conn.close();
				 }
			 } catch (SQLException e) {
				 // TODO Auto-generated catch block
				 e.printStackTrace();
			 }
		 }
		 return null;
	 }

	 public <T> T execute(PrepareOption<T> prepareOption, String sql) {

		 Connection conn = null;
		 PreparedStatement pstmt = null;
		 try {

			 conn = createConnection();
			 pstmt = conn.prepareStatement(sql);
			 return prepareOption.doPreparedStatement(pstmt);
		 }catch(Exception e) {
			 e.printStackTrace();
		 }finally {
			 try {
			 	if(null != pstmt){
					pstmt.close();
				}
				if(null != conn){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		 }
		 return null;

	 }
	 private boolean dropTable(String tableName){
	 	String sql = "DROP TABLE IF EXISTS "+tableName+";";
	 	return execute(new StatementOption<Boolean>() {
			@Override
			public Boolean doStatement(Statement statement, String sql) throws SQLException {
				statement.execute(sql);
				return true;
			}
		},sql);
	 }
	private Boolean createTable(TableInfo tableInfo, TargetCallback callback){

			Boolean flag = false;
			String tableName = tableInfo.getNameen();
			List<FieldInfo> fieldInfoList = tableInfo.getFieldList();
			//如果表存在，先删除表
			if(dropTable(tableName)){
				StringBuffer sb = new StringBuffer();
				sb.append("CREATE TABLE "+tableName+" (");
				int index = 0;
				int size = fieldInfoList.size();
				for(FieldInfo fieldInfo:fieldInfoList){
					String columnName = fieldInfo.getColumnName();
					String columnType = fieldInfo.getColumnType();
					String nullable = NullableEnum.NULLABLE.getValue();
					String columnKey = fieldInfo.getColumnKey();

					if(fieldInfo.getIsNullable().equals(YesOrNoEnum.NO.getValue())){
						nullable = NullableEnum.NOTNULL.getValue();
					}
					sb.append(columnName+" "+columnType+" "+nullable);
					if("PRI".equals(columnKey)){
						sb.append(" PRIMARY KEY");

					}
					if(index != size -1){
						sb.append(",");

					}
					if(index == size -1){
						sb.append(");");
					}
					index++;

				}

				String sql = sb.toString();

				flag = execute(new StatementOption<Boolean>() {
					@Override
					public Boolean doStatement(Statement statement, String sql) throws SQLException {
						 statement.execute(sql);
						 return true;
					}
				},sql);
				String msg = "目标表名【"+sql+"】创建成功";
				if(null != callback){
					callback.onTableGenerated(msg);
				}

			}
			return flag;
	 }


	 private Boolean insertData(TableInfo tableInfo,TargetCallback callback){

	 	List<Map<String,Object>> mapList = tableInfo.getMapList();
	 	List<FieldInfo> fieldInfoList = tableInfo.getFieldList();
	 	String tableName = tableInfo.getNameen();
	 	String placeholder = "";
	 	int size = fieldInfoList.size();
	 	for(int i=0;i<size;i++){
	 		placeholder += "?,";
		}
	 	placeholder = placeholder.substring(0,placeholder.length()-1);
	 	String sql = "insert into "+ tableName+" values("+placeholder+")";

	 	return execute(new PrepareOption<Boolean>() {
			@Override
			public Boolean doPreparedStatement(PreparedStatement pstmt) throws SQLException {

				for(Map<String,Object> map:mapList){
					String values = "";
					for (int i=0;i<size;i++){
						FieldInfo fieldInfo = fieldInfoList.get(i);
						String columnName = fieldInfo.getColumnName();
						String dateType = fieldInfo.getDataType();
						if(DataTypeEnum.VARCHAR.value.equals(dateType)){
							pstmt.setString(i+1, (String) map.get(columnName));
						}else if(DataTypeEnum.DATE.value.equals(dateType)){
							pstmt.setDate(i+1, (java.sql.Date) map.get(columnName));
						}else if(DataTypeEnum.TIME.value.equals(dateType)){
							pstmt.setTime(i+1, (Time) map.get(columnName));
						}else if(DataTypeEnum.DATETIME.value.equals(dateType)){
							pstmt.setDate(i+1, (java.sql.Date) map.get(columnName));
						}else if(DataTypeEnum.DECIMAL.value.equals(dateType)){
							pstmt.setBigDecimal(i+1, (BigDecimal) map.get(columnName));
						}else if(DataTypeEnum.DOUBLE.value.equals(dateType)){
							pstmt.setDouble(i+1, (Double) map.get(columnName));
						}else if(DataTypeEnum.FLOAT.value.equals(dateType)){
							pstmt.setFloat(i+1, (Float) map.get(columnName));
						}else if(DataTypeEnum.INT.value.equals(dateType)){
							pstmt.setInt(i+1, (Integer) map.get(columnName));
						}else if(DataTypeEnum.MEDIUMTEXT.value.equals(dateType)){

							pstmt.setString(i+1, (String) map.get(columnName));
						}else if(DataTypeEnum.TEXT.value.equals(dateType)){
							pstmt.setString(i+1, (String) map.get(columnName));
						}else if(DataTypeEnum.CHAR.value.equals(dateType)){
							pstmt.setString(i+1, (String) map.get(columnName));
						}else if(DataTypeEnum.LONGTEXT.value.equals(dateType)){
							pstmt.setString(i+1, (String) map.get(columnName));
						}else{
							System.out.println(dateType);
						}

					}
					pstmt.addBatch();
				}
				pstmt.executeBatch();
				String msg = "目标表名【"+tableName+"】数据已插入！";
				if(null != callback){
					callback.onDataInserted(msg);
				}
				return true;

			}
		},sql);
	 }

	 public void generateTableAndData(JdbcConfig config,TableInfo tableInfo,TargetCallback callback){
		 if(null != config){
			 this.config = config;
		 }
	 	Boolean flag = getInstance().createTable(tableInfo,callback);
		 if(null != flag && flag){
			 getInstance().insertData(tableInfo,callback);
		 }

	 }

	 public static void main(String args[]) {
		 

	 }
	 
	
	
}
